package com.om.common;

/**   
* @CopyRright (c) 
* @Title:SqlServerBeanFactory.java
* @Description: 实现了Sql Server的分页查询以及执行存储过程查询
* @author tangchuan
* @version V1.0   
*/ 

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.om.base.AbstractBean;
import com.om.base.BeanFactory;
import com.om.base.SqlFilter;
import com.om.base.Utils;
import com.om.pages.PageInfo;

public class SqlServerBeanFactory extends BeanFactory {
	public <T> List<T> getBeanList(Class<?> beanClass,SqlFilter filter, PageInfo page) {
		String sql = "";
		List<T> beanList = null;
		int startIndex = page.pageSize * page.currentPage;

		beanList = getBeanList(beanClass, filter, startIndex, page.pageSize);
		return beanList;
	}

	public <T> List<T>  getBeanList(SqlFilter filter, String orderBy) {
		List<T> beanList = null;
		String sql = "";
		String className = "";
		PreparedStatement pst = null;
		java.sql.CallableStatement call=null;
		ResultSet rs = null;
		try {
			beanList = new ArrayList<T>();
			if(this.connection ==null){
				this.connection = Utils.GetOpenedConnection();
			}
			sql = bean.generateQuerySql(filter);
			
			className = this.bean.getClassName();
			if (filter == null) {
				filter = new SqlFilter();
			}
			if(sql.indexOf("CALL")==-1){
				AdjustFilter(filter);
			}
		
			sql = bean.generateQuerySql(filter);
			if (orderBy != null) {
				sql += orderBy;
			}
			
			List<String> lstField = filter.getFieldList();
			List lstValue = filter.getValueList();
			
			if(sql.indexOf("CALL")!=-1){
				
				call = this.connection.prepareCall(sql);
				for(int i =0;i<lstValue.size();i++){
					call.setObject(i+1, lstValue.get(i)+"");
				}
				rs = call.executeQuery();
				//pst2 .setInt(1, year);
				//.beanList.rs = pst2.executeQuery();
			}
			else {
				pst = prepareSql(sql, lstField, lstValue);
				rs = pst.executeQuery();
			}
			
			
			
			ResultSetMetaData met = rs.getMetaData();
			while (rs.next()) {
				AbstractBean item = (AbstractBean) Class.forName(className).newInstance();
				/*if (item.Field.length == 0) {
					item.Field = new String[met.getColumnCount()];
					for (int i = 0; i < met.getColumnCount(); ++i) {
						String column = met.getColumnLabel(i + 1);
						item.Field[i] = column;
					}
				}*/
				
				
				AbstractBean.CopyConfig(bean, item);
				
				for (int i = 0; i < item.Field.length; ++i) {
					Object value = null;
					String columnName = item.Field[i];
					String columnTypeName = met.getColumnTypeName(i + 1);
					if (columnTypeName.indexOf("BLOB") != -1) {
						value = rs.getAsciiStream(columnName);
					} else if (columnTypeName.indexOf("TINYINT") != -1) {
						value = rs.getShort(columnName);
					} else {
						value = rs.getObject(columnName);
					}

					item.set(columnName, value);
				}
				

				beanList.add((T) item);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return beanList;
	}

	public <T> List<T> getBeanList(Class<?> beanClass,SqlFilter filter, int startIndex, int pageSize) {
		
		List<T>beanList = new ArrayList<T>();
		PreparedStatement pst = null;
		String className = "";
		ResultSet rs = null;
		String sql = "";
		try {
			className = beanClass.getName();
			bean = (BaseBean) Class.forName(className).newInstance();
			bean.connection = this.connection;
			bean.initColumns();
			sql = this.bean.generateQuerySql(filter);
			
			if (filter == null) {
				filter = new SqlFilter();
			}
			if(!bean.TABLE_NAME.equals("")){
				AdjustFilter(filter);
			}
			

			if (this.orderBy != null) {
				sql += this.orderBy;
			}
			String reverseOrderBy = "";
			if(orderBy.indexOf("desc")!=-1){
				reverseOrderBy =orderBy.replace("desc", "asc");
			}
			else  if(orderBy.indexOf("asc")!=-1){ 
				reverseOrderBy =orderBy.replace("asc", "desc");
			}
			int topNum =startIndex+pageSize;
			sql =sql.replace("select ", "select top "+topNum+" ");
			sql="select top  "+ pageSize +" "+bean.PrimaryKey+" from ("+ sql +") a " + reverseOrderBy;
			sql=bean.generateQuerySql()+ " where "+bean.PrimaryKey +" in ("+sql+") " + this.orderBy; 
			//sql = sql + " limit " + startIndex + "," + pageSize;
			
			List<String> lstField = filter.getFieldList();
			List lstValue = filter.getValueList();

			pst = prepareSql(sql, lstField, lstValue);

			rs = pst.executeQuery();
			ResultSetMetaData met = rs.getMetaData();
			while (rs.next()) {
				try{
					AbstractBean item = (AbstractBean) Class.forName(className).newInstance();
					/*if (item.Field.length!=met.getColumnCount()) {
						item.Field = new String[met.getColumnCount()];
						for (int i = 0; i < met.getColumnCount(); ++i) {
							String column = met.getColumnLabel(i + 1);
							item.Field[i] = column;
						}
					}*/
					
					AbstractBean.CopyConfig(bean, item);
					
					for (int i = 0; i < item.Field.length; ++i) {
						Object value = null;
						String columnName = item.Field[i];
						String columnTypeName = met.getColumnTypeName(i + 1);
						if (columnTypeName.indexOf("BLOB") != -1) {
							value = rs.getAsciiStream(columnName);
						} else if (columnTypeName.indexOf("TINYINT") != -1) {
							value = rs.getShort(columnName);
						} else {
							value = rs.getObject(columnName);
						}
						item.set(columnName, value);
						
					}
					beanList.add((T) item);
				}catch(Exception ex1){
					ex1.printStackTrace();
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		}

		return beanList;
	}
	
	public PreparedStatement prepareCallableStatement(String sql, List<String> lstField,
			List<?> lstValue) {
		PreparedStatement pst = null;

		Map<String, String> sqlparamHashtable = new LinkedHashMap<String, String>();

		try {
			Hashtable<String, Integer> typeMap = getFieldType();
			Hashtable<String, String> columnTypeMap = getColumnTypeNames();
			pst = this.connection.prepareStatement(sql);
			if (lstField != null && lstValue != null) {
				for (int i = 0; i < lstField.size(); ++i) {
					String field = (String) lstField.get(i).replace("`", "");

					if (lstValue.size() > i) {
						Pattern p = Pattern.compile("(\\^\\d+)");
						Matcher m = p.matcher(field);

						if (m.find()) {
							field = m.replaceAll("");
						}

						p = Pattern.compile("(\\w+\\.)");
						m = p.matcher(field);
						if (m.find()) {
							field = m.replaceAll("");
						}

						Object value = lstValue.get(i);
						if (value != null) {
							sqlparamHashtable.put(field, value.toString());
						} else {
							sqlparamHashtable.put(field, "null");
						}

						String columnTypeName = "";

						Integer sqlType = (Integer) typeMap.get(field);
						if (sqlType == null) {
							sqlType = Integer.valueOf(12);
						}
						if (columnTypeMap.containsKey(field)) {
							columnTypeName = (String) columnTypeMap.get(field);
						}

						if (value == null) {
							pst.setNull(i + 1, sqlType);
						} else if (columnTypeName.indexOf("TINYINT") != -1) {
							pst.setShort(i + 1,
									Short.parseShort(value.toString()));
						} else {
							pst.setObject(i + 1, value, sqlType, 4);
						}
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		
		return pst;
	}
}

